﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ShopB2C.Entity;
using ShopB2C.ViewModels;
using Valor.DBUtility.MSSQLHelper;
using xTeam.HengLe.Entity;
using xTeam.HengLe.Enums;

namespace ShopB2C.SQLDataAccess
{
    public class OrderAccess
    {
        #region procedure name

        /// <summary>
        /// 从购物车增加到订单，并且删除购物车
        /// </summary>
        private const string _addToOrder = "Order_Sale_Append";
        /// <summary>
        /// 销售单列表
        /// </summary>
        private const string _saleList = "Order_Sale_List";
        /// <summary>
        /// 销售订单预览
        /// </summary>
        private const string _saleView = "Order_Sale_View";
        /// <summary>
        /// 产品的历史购买记录
        /// </summary>
        private const string _saleHistory = "Order_Sale_HistoryList";
        /// <summary>
        /// 删除订单
        /// </summary>
        private const string _saleDelete = "Order_Sale_DeleteItem";
        /// <summary>
        /// 修改订单状态，写入发货消息
        /// </summary>
        private const string _saleProcess = "Order_Sales_Process";

        /// <summary>
        /// 查询生成的订单预览
        /// </summary>
        private const string _orderPreview = "Order_Sale_Preview";

        /// <summary>
        /// 获取所有的寄货方式
        /// </summary>
        private const string _expressList = "PN_Express_List";

        /// <summary>
        /// 支付宝通知接口返回的数据处理
        /// </summary>
        private const string _OrderPayProcess = "Order_Pay_Add";

        /// <summary>
        /// 用户消费记录
        /// </summary>
        private const string _costRecord = "Cost_Record_Get";

        #endregion

        private bool _isCN = true;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionstring">数据库链接字符串</param>
        public OrderAccess(string connectionstring, string langName)
        {
            SQLProcedureHelper.ConnectionString = connectionstring;
            this._isCN = langName.ToLower() == "en-us" ? false : true;
        }
        /// <summary>
        /// 将购物车增加到订单
        /// </summary>
        /// <param name="addressId">收货地址ID</param>
        /// <param name="uid">关联的用户ID</param>
        /// <param name="orderId">订单ID</param>
        /// <returns>受影响的行</returns>
        public int AddToOrder(string orderId, int addressId, int uid, int cartType)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@SaleId",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Uid",SqlDbType.Int)
                ,new SqlParameter("@Description",SqlDbType.NVarChar,50)
                ,new SqlParameter("@AddressId",SqlDbType.Int)
                ,new SqlParameter("@CartType",SqlDbType.Int){Value = cartType}
            };
            parameters[0].Value = orderId;
            parameters[1].Value = uid;
            parameters[2].Value = string.Empty;
            parameters[3].Value = addressId;

            result = SQLProcedureHelper.ExecuteNonQuery(string.Format("{0}{1}", _addToOrder, _isCN ? string.Empty : "_en"), parameters);
            return result;
        }
        /// <summary>
        /// 订单列表
        /// </summary>
        /// <param name="uid">关联的用户ID</param>
        /// <param name="pageIndex">分页页码</param>
        /// <returns>订单列表</returns>
        public List<OrderEntity> SaleList(int uid, int pageIndex, ref int pageCount,OrderState state=OrderState.All,string orderId="")
        {
            List<OrderEntity> orderList = new List<OrderEntity>();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uid",SqlDbType.Int){Value = uid}
                ,new SqlParameter("@PageIndex",SqlDbType.Int){Value = pageIndex}
                ,new SqlParameter("@state",(int)state)
                ,new SqlParameter("@orderId",orderId)
            };

            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(string.Format("{0}{1}", _saleList, _isCN ? string.Empty : "_en"), parameters);
            while (reader.Read())
            {
                pageCount = Convert.ToInt32(reader["PageCount"]);
                orderList.Add(new OrderEntity()
                {
                    OrderId = reader["OrderId"].ToString(),
                    Price = Convert.ToDecimal(reader["Price"]),
                    Count = Convert.ToInt32(reader["Count"]),
                    AddDate = Convert.ToDateTime(reader["AddDate"]),
                    State = Convert.ToInt32(reader["State"]),
                    Uid = Convert.ToInt32(reader["Uid"])
                });
            }
            reader.Close();

            return orderList;
        }
        /// <summary>
        /// 订单预览
        /// </summary>
        /// <param name="uid">关联的用户ID</param>
        /// <param name="orderId">订单ID</param>
        /// <returns>订单对象预览</returns>
        public OrderViewModel SaleOrderView(int uid, string orderId)
        {
            OrderViewModel order = null;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uid",SqlDbType.Int),
                new SqlParameter("@OrderId",SqlDbType.NVarChar,50)
            };

            parameters[0].Value = uid;
            parameters[1].Value = orderId;

            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(string.Format("{0}{1}", _saleView, _isCN ? string.Empty : "_en"), parameters);

            if (reader.HasRows)
            {
                order = new OrderViewModel();
                order.ProductList = new List<EProduct>();
                order.OrderView = new OrderEntity();
                order.Address = new UserAddressEntity();
                order.Express = new ExpressEntity();

                #region 第一个reader 读取订单信息
                while (reader.Read())
                {
                    // 关联的产品列表
                    order.ProductList.Add(new EProduct()
                    {
                        ProductId = Convert.ToInt32(reader["ProductId"]),
                        Description = reader["Property"].ToString(),
                        ProductName = reader["ProductName"].ToString(),
                        ProductNo = reader["ProductNo"].ToString(),
                        Price = Convert.ToDecimal(reader["Price"]),
                        DisCount = Convert.ToDecimal(reader["Discount"]),
                        SalesCount = Convert.ToInt32(reader["Count"]),
                    });
                    /// 关联的订单信息
                    order.OrderView.OrderId = reader["OrderId"].ToString();
                    order.OrderView.Description = reader["OrderDesc"].ToString();
                    order.OrderView.AddDate = Convert.ToDateTime(reader["AddDate"]);
                    order.OrderView.Uid = Convert.ToInt32(reader["Uid"]);
                    order.OrderView.State = Convert.ToInt32(reader["state"]);
                }
                // 订单总金额
                order.OrderView.Price = order.ProductList.Sum(p => p.SalesCount * p.Price * (decimal)p.DisCount);
                // 订单总件数
                order.OrderView.Count = order.ProductList.Sum(p => p.SalesCount);

                #endregion

                #region 第二个reader关联收货地址

                reader.NextResult();
                if (reader.HasRows)
                {
                    reader.Read();
                    // 关联的收货地址信息
                    //order.Address.AddressId = Convert.ToInt32(reader["AddressId"]);
                    order.Address.ReallyName = reader["ReallyName"].ToString();
                    // order.Address.Uid = Convert.ToInt32(reader["Uid"]);
                    order.Address.Address = reader["Address"].ToString();
                    order.Address.Description = reader["Description"].ToString();
                    order.Address.Mobile = reader["Mobile"].ToString();
                    order.Address.Phone = reader["Phone"].ToString();
                    order.Address.PostNo = reader["PostNo"].ToString();


                }

                #endregion

                #region 第三个reader读取货运信息

                reader.NextResult();
                if (reader.HasRows)
                {
                    reader.Read();
                    order.Express.ExpressNo = reader["ExpressNo"].ToString();
                    order.Express.Description = reader["Description"].ToString();
                }

                #endregion
            }
            reader.Close();

            return order;
        }

        /// <summary>
        /// 即将生成的订单的预览页面
        /// </summary>
        /// <param name="uid">
        /// 用户编号
        /// </param>
        /// <param name="productId">
        /// 产品Id
        /// 订单有两种方式
        /// 1.productId=0表示通过购物车生成订单
        /// 2.productId!=0表示生成该产品Id的订单
        /// </param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public OrderViewModel OrderPreivew(int uid,int productid,int pageIndex) 
        {
            OrderViewModel model = null;
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@uid",uid),
                new SqlParameter("@productid",productid),
                new SqlParameter("@pageIndex",pageIndex)
            };
            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_orderPreview, paras);
            if (reader.HasRows)
            {
                model = new OrderViewModel();
                model.ProductList = new List<EProduct>();
                model.OrderView = new OrderEntity();
                model.AddressList = new List<UserAddressEntity>();
                model.Express = new ExpressEntity();

                #region 第一个结果集产品列表
                while (reader.Read())
                {
                    model.ProductList.Add(
                        new EProduct() { 
                            ProductId=Convert.ToInt32(reader["productid"]),
                            ProductName = reader["ProductName"].ToString(),
                            SubName = reader["SubName"].ToString(),
                            Description = reader["Description"].ToString(),
                            KeyWords = reader["KeyWords"].ToString(),
                            Price = Convert.ToDecimal(reader["Price"]),
                            ProductNo = reader["ProductNo"].ToString(),
                            DisCount=Convert.ToDecimal(reader["Discount"]),
                            BrandName = reader["brandname"].ToString(),
                            SalesCount = Convert.ToInt32(reader["Count"]),
                            Property = reader["Property"].ToString()
                        }
                    );
                }
                #endregion

                #region 第二个结果集用户收货地址
                reader.NextResult();
                while (reader.Read())
                {
                    model.AddressList.Add(
                        new UserAddressEntity() {
                            AddressId = Convert.ToInt32(reader["AddressId"]),
                            ReallyName = reader["ReallyName"].ToString(),
                            Address = reader["Address"].ToString(),
                            Mobile = reader["Mobile"].ToString(),
                            Phone = reader["Phone"].ToString(),
                            PostNo = reader["PostNo"].ToString()
                        }
                    );
                }
                #endregion
            }
            reader.Close();
            /*
            for (int i = 0; i < model.ProductList.Count;i++ )
            {
                model.OrderView.Count += model.ProductList[i].SalesCount;
                model.OrderView.Price += model.ProductList[i].SalesCount * (model.ProductList[0].Price * model.ProductList[0].DisCount);
            }
            */
            model.OrderView.Price = model.ProductList.Sum(e => e.SalesCount * (e.Price * e.DisCount));
            model.OrderView.Count = model.ProductList.Sum(e => e.SalesCount);
            return model;
        }

        /// <summary>
        /// 商品的历史购买记录
        /// </summary>
        /// <param name="productId">关联的产品ID</param>
        /// <returns>历史记录信息</returns>
        public List<UserEntity> SaleHistoryList(int productId)
        {
            List<UserEntity> list = new List<UserEntity>();

            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(
               string.Format("{0}{1}", _saleHistory, _isCN ? string.Empty : "_en")
                , new SqlParameter[] { new SqlParameter("@ProductId", SqlDbType.Int) { Value = productId } });

            while (reader.Read())
            {
                list.Add(new UserEntity()
                {
                    NickName = reader["NickName"].ToString(),
                    Uid = Convert.ToInt32(reader["Count"]),
                    Uname = Convert.ToDecimal(reader["Price"]).ToString("0.00"),
                    RegTime = Convert.ToDateTime(reader["AddDate"])
                });
            }
            reader.Close();
            return list;
        }
        /// <summary>
        /// 删除订单
        /// </summary>
        /// <param name="orderId">订单ID</param>
        /// <param name="uid">用户ID</param>
        /// <returns>返回操作码</returns>
        public int SaleDeleteItem(string orderId, int uid)
        {
            return Convert.ToInt32(SQLProcedureHelper.ExecuteScalar(
                _saleDelete
                , new SqlParameter[]{
                    new SqlParameter("@OrderId",SqlDbType.NVarChar,50){Value = orderId}
                    ,new SqlParameter("@Uid",SqlDbType.Int){Value =uid}}
                    ));
        }
        /// <summary>
        /// 订单处理
        /// </summary>
        /// <param name="state">需要更改到的状态</param>
        /// <param name="expressNo">快递单号</param>
        /// <param name="description">处理说明</param>
        /// <param name="orderId">关联的订单ID</param>
        /// <param name="uid">关联的用户ID</param>
        /// <returns>受影响的行</returns>
        public int SalesProcess(int state, string expressNo, string description, string orderId, int uid)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@State",SqlDbType.Int){ Value =state },
                new SqlParameter("@ExpressNo",SqlDbType.NVarChar,50){ Value = expressNo },
                new SqlParameter("@SaleId",SqlDbType.NVarChar,50){ Value = orderId },
                new SqlParameter("@Description",SqlDbType.NVarChar,500){ Value = description }
            };
            result = SQLProcedureHelper.ExecuteNonQuery(_saleProcess, parameters);
            return result;
        }

        /// <summary>
        /// 支付宝通知接口返回的数据处理
        /// </summary>
        /// <param name="orderPay"></param>
        /// <returns></returns>
        public int OrderPayProcess(EOrderPay orderPay)
        {
            int result = 0;
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@trade_no",orderPay.TradeNo),
                new SqlParameter("@sale_id",orderPay.SaleId),
                new SqlParameter("@price",orderPay.Price),
                new SqlParameter("@subject",orderPay.Subject),
                new SqlParameter("@body",orderPay.Body),
                new SqlParameter("@buyer_email",orderPay.BuyerEmail),
                new SqlParameter("@trade_status",orderPay.TradeStatus)
            };
            result = SQLProcedureHelper.ExecuteNonQuery(_OrderPayProcess, paras);
            return result;
        }

        /// <summary>
        /// 获取用户消费记录
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<ECostRecord> GetCostRecord(int uid=-1)
        { 
            List<ECostRecord> records=new List<ECostRecord>();
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@uid",uid)
            };
            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_costRecord, paras);
            while(reader.Read())
            {
                records.Add(new ECostRecord() { 
                    Price=Convert.ToDecimal(reader["price"]),
                    CostItem=reader["cost_item"].ToString(),
                    OrderId = reader["orderid"].ToString(),
                    Cost_Time=Convert.ToDateTime(reader["cost_time"])
                });
            }
            reader.Close();
            return records;
        }

        /// <summary>
        /// 获取所有的寄货方式
        /// </summary>
        /// <returns></returns>
        public List<ExpressEntity> ExpressList() 
        {
            List<ExpressEntity> expressList = null;
            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_expressList);
            if(reader.HasRows)
            {
                expressList = new List<ExpressEntity>();
                while (reader.Read())
                {
                    expressList.Add(
                        new ExpressEntity() {
                            ExpressId = Convert.ToInt32(reader["ExpressId"]),
                            ExpressName = reader["ExpressName"].ToString(),
                            Description = reader["Description"].ToString(),
                            Price=Convert.ToDecimal(reader["Price"])
                        }
                    );
                }
            }
            return expressList;
        }
    }
}
